package dao;

import java.io.Serializable;
import java.sql.ResultSet;

import TouristManagementBO.Others.ServerNaming;

public class StatistiqueGeneraleDAO implements Serializable {
	MySqlConnecte myConn = new  MySqlConnecte();
	
	//S�quences de visite favorites
	public String getSequenceFavorites(){
		try{
			//Get maximum Sequence Favorites
			String sqlCommand = "SELECT COUNT(Sequence) AS Compter FROM SequenceVisite GROUP BY Sequence HAVING Sequence!=''  ORDER BY Compter DESC LIMIT 1";
			ResultSet rs = myConn.getList(sqlCommand);
			String maxSF=null;
			if (rs.next()){
				maxSF=rs.getString(1);
			}
			
			//Get Sequence Favorites
			sqlCommand = "SELECT Sequence FROM SequenceVisite GROUP BY Sequence HAVING COUNT(Sequence)="+maxSF+"";
			rs = myConn.getList(sqlCommand);
			String res=null;
			if (rs.next()){
				res=rs.getString(1);
			}
			return res;
		} catch(Exception e) {
			// Il y avait une erreur
			System.out.println("Error of calculation SequenceVisiteFavorite");
			return null;
		}
	}
	
	//Get taux de satisfaction moyen
	public short getSatisfactionMoyen(){
		SiteVisiteDAO leSite1 = new SiteVisiteDAO(ServerNaming.get_nomSite1());
		SiteVisiteDAO leSite2 = new SiteVisiteDAO(ServerNaming.get_nomSite2());
		SiteVisiteDAO leSite3 = new SiteVisiteDAO(ServerNaming.get_nomSite3());
		SiteVisiteDAO leSite4 = new SiteVisiteDAO(ServerNaming.get_nomSite4());
		SiteVisiteDAO leSite5 = new SiteVisiteDAO(ServerNaming.get_nomSite5());
		SiteVisiteDAO leSite6 = new SiteVisiteDAO(ServerNaming.get_nomSite6());
		
		return (short) ((leSite1.getMoyenneAvis() +
				leSite2.getMoyenneAvis() +
				leSite3.getMoyenneAvis() +
				leSite4.getMoyenneAvis() +
				leSite5.getMoyenneAvis() +
				leSite6.getMoyenneAvis() ) / 6);
		
	}
	
	
	//Get taux de satisfaction moyen
		public short getSatisfactionMoyen2(short nbSite){
			try{
				//Get total de niveau satisfaction
				String tableSite = null;
				short totalNS=0;
				short totalNSSite=0;
				short totalVisite=0;
				short totalVisiteSite=0;
				ResultSet rs =null;
				String sqlCommand=null;
				
				for (int i=1;i<=nbSite;i++){
					tableSite = "Site" + String.valueOf(i);
					//Get total de niveau satisfaction (totalNS) des site
					sqlCommand = "SELECT SUM(NiveauSatisfaction) FROM "+tableSite+" WHERE NiveauSatisfaction is not null";
					rs = myConn.getList(sqlCommand);
					if (rs.next()){
						totalNSSite=rs.getShort(1);
					}
					totalNS= (short)(totalNS + totalNSSite);
					
					//Get total visite (totalVisite) des sites
					sqlCommand = "SELECT COUNT(*) FROM "+tableSite+" WHERE NiveauSatisfaction is not null";
					rs = myConn.getList(sqlCommand);
					if (rs.next()){
						totalVisiteSite=rs.getShort(1);
					}
					totalVisite=(short)(totalVisite + totalVisiteSite);
				}
				short res= (short)Math.round(totalNS/totalVisite);
				return res;
			} catch(Exception e) {
				// Il y avait une erreur
				System.out.println("Error of calculation: SatisfactionMoyen");
				return -1;
			}
		}
}
